/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package my12306.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import my12306.table.KeyValueItem;
import org.slf4j.LoggerFactory;

/**
 *
 * @author Jack
 */
public class SQLite {

    private final static org.slf4j.Logger log = LoggerFactory.getLogger(SQLite.class);
    static Connection connection = null;
    private final static String DefaultDb = "cities";

    static {
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException ex) {
            log.error(ex.getLocalizedMessage(), ex);
        }
    }

    public static Connection getConn() {
        return getConn(DefaultDb);
    }

    public static Connection getConn(String db) {
        try {
            if (connection == null || connection.isClosed()) {
                if (db == null || db.isEmpty()) {
                    return null;
                }
                try {
                    connection = DriverManager.getConnection("jdbc:sqlite:" + db);
                } catch (SQLException ex) {
                    log.error(ex.getLocalizedMessage(), ex);
                }
            }
            log.info("Connection created: {}.", db);
            return connection;
        } catch (SQLException ex) {
            log.error(ex.getLocalizedMessage(), ex);
        }
        return null;
    }

    public static void close() {
        try {
            if (connection != null) {
                connection.close();
                log.info("Connection closed..");
            }
        } catch (SQLException ex) {
            log.error(ex.getLocalizedMessage(), ex);
        }
    }

    public static ArrayList<KeyValueItem> getCityNameFromDb(String prefix) {
        ArrayList<KeyValueItem> items = new ArrayList<KeyValueItem>();
        try {
            Connection conn = getConn("cities");
            Statement statement = conn.createStatement();
            statement.setQueryTimeout(30);
            String sql = "select * from city where py3 like '%s%%' order by pyfull,name";
            sql = String.format(sql, prefix.toLowerCase());
//            log.info("sql: " + sql);
            ResultSet rs = statement.executeQuery(sql);

            while (rs.next()) {
                items.add(new KeyValueItem(rs.getString("name"), rs.getString("py3")));
//                log.info("{},{},{},{},{},{}", rs.getString("id"), rs.getString("py3"), rs.getString("name"),
//                        rs.getString("py2"), rs.getString("pyfull"), rs.getString("py1"));
            }
        } catch (SQLException ex) {
            log.error(ex.getLocalizedMessage(), ex);
        } finally {
//            try {
//                if (getConn("cities") != null) {
//                    getConn("cities").close();
//                }
//            } catch (SQLException ex) {
//                log.error(ex.getLocalizedMessage(), ex);
//            }
        }

        return items;
    }

    public static String getCityCode(String cityName) {
        String result = "";
        try {
            Connection conn = getConn("cities");
            Statement statement = conn.createStatement();
            statement.setQueryTimeout(30);
            String sql = "select py2 from city where name = '%s'";            
            sql = String.format(sql, cityName);
            //log.info(sql);
            ResultSet rs = statement.executeQuery(sql);
            result = rs.getString("py2");
        } catch (SQLException ex) {
            log.error(ex.getLocalizedMessage(), ex);
        } finally {
//            try {
//                if (getConn("cities") != null) {
//                    getConn("cities").close();
//                }
//            } catch (SQLException ex) {
//                log.error(ex.getLocalizedMessage(), ex);
//            }
        }

        return result;
    }
}
